package com.iplant.aps.serviceimpl.dao.wms;

import com.iplant.aps.service.mesenum.APSOutSourceType;
import com.iplant.aps.service.mesenum.APSReplaceType;
import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.Pagination;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.excel.MyExcelSheet;
import com.iplant.aps.service.po.wms.WMSLineEdgeLibraryItem;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import com.iplant.aps.serviceimpl.utils.aps.APSConstans;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class WMSLineEdgeLibraryItemDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(WMSLineEdgeLibraryItemDAO.class);

    private static WMSLineEdgeLibraryItemDAO Instance = null;

    private WMSLineEdgeLibraryItemDAO() {
        super();
    }

    public static WMSLineEdgeLibraryItemDAO getInstance() {
        if (Instance == null)
            Instance = new WMSLineEdgeLibraryItemDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, WMSLineEdgeLibraryItem wWMSLineEdgeLibraryItem,
                      OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wWMSLineEdgeLibraryItem == null)
                return 0;

            String wSQL = "";
            if (wWMSLineEdgeLibraryItem.getID() <= 0) {
                wSQL = MessageFormat
                        .format("INSERT INTO {0}.wms_lineedgelibraryitem(WMSLineEdgeID,ProductID,LineID,CustomerID,"
                                        + "OrderID,WBSNo,PartNo,PartID,PartPointID,MaterialID,FQTY,CreateTime," +
                                        "UseTrackingNumber,IsPost,PostMsg,APSBOMItemID) VALUES(:WMSLineEdgeID,"
                                        + ":ProductID,:LineID,:CustomerID,:OrderID,:WBSNo,:PartNo,:PartID," +
                                        ":PartPointID," +
                                        ":MaterialID,"
                                        + ":FQTY,:CreateTime,UseTrackingNumber,:IsPost,:PostMsg,:APSBOMItemID);",
                                wInstance.Result);
            } else {
                wSQL = MessageFormat.format(
                        "UPDATE {0}.wms_lineedgelibraryitem SET WMSLineEdgeID = :WMSLineEdgeID,ProductID = :ProductID,"
                                + "LineID = :LineID,CustomerID = :CustomerID,OrderID = :OrderID,WBSNo = :WBSNo,PartNo" +
                                " = :PartNo,"
                                + "PartID = :PartID,PartPointID = :PartPointID,MaterialID = :MaterialID,FQTY = :FQTY,"
                                + "CreateTime = :CreateTime,UseTrackingNumber=:UseTrackingNumber,IsPost=:IsPost," +
                                "PostMsg=:PostMsg,APSBOMItemID=:APSBOMItemID WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wWMSLineEdgeLibraryItem.ID);
            wParamMap.put("WMSLineEdgeID", wWMSLineEdgeLibraryItem.WMSLineEdgeID);
            wParamMap.put("ProductID", wWMSLineEdgeLibraryItem.ProductID);
            wParamMap.put("LineID", wWMSLineEdgeLibraryItem.LineID);
            wParamMap.put("CustomerID", wWMSLineEdgeLibraryItem.CustomerID);
            wParamMap.put("OrderID", wWMSLineEdgeLibraryItem.OrderID);
            wParamMap.put("WBSNo", wWMSLineEdgeLibraryItem.WBSNo);
            wParamMap.put("PartNo", wWMSLineEdgeLibraryItem.PartNo);
            wParamMap.put("PartID", wWMSLineEdgeLibraryItem.PartID);
            wParamMap.put("PartPointID", wWMSLineEdgeLibraryItem.PartPointID);
            wParamMap.put("MaterialID", wWMSLineEdgeLibraryItem.MaterialID);
            wParamMap.put("FQTY", wWMSLineEdgeLibraryItem.FQTY);
            wParamMap.put("CreateTime", wWMSLineEdgeLibraryItem.CreateTime);
            wParamMap.put("UseTrackingNumber", wWMSLineEdgeLibraryItem.UseTrackingNumber);
            wParamMap.put("IsPost", wWMSLineEdgeLibraryItem.IsPost);
            wParamMap.put("PostMsg", wWMSLineEdgeLibraryItem.PostMsg);
            wParamMap.put("APSBOMItemID", wWMSLineEdgeLibraryItem.APSBOMItemID);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wWMSLineEdgeLibraryItem.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wWMSLineEdgeLibraryItem.setID(wResult);
            } else {
                wResult = wWMSLineEdgeLibraryItem.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<WMSLineEdgeLibraryItem> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (WMSLineEdgeLibraryItem wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.wms_lineedgelibraryitem WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public WMSLineEdgeLibraryItem SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        WMSLineEdgeLibraryItem wResult = new WMSLineEdgeLibraryItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<WMSLineEdgeLibraryItem> wList = SelectList(wLoginUser, wID, -1, -1, -1, -1, -1, -1, -1, -1, null, null,
                    wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<WMSLineEdgeLibraryItem> SelectList(BMSEmployee wLoginUser, int wID, int wWMSLineEdgeID, int wProductID,
                                                   int wLineID, int wCustomerID, int wOrderID, int wPartID,
                                                   int wPartPointID, int wMaterialID,
                                                   Calendar wStartTime, Calendar wEndTime,
                                                   OutResult<Integer> wErrorCode) {
        List<WMSLineEdgeLibraryItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT t.*,t5.ReplaceType,t5.OutSourceType,t1.ProjectStockNo FROM {0}" +
                            ".wms_lineedgelibraryitem t "
                            + " left join {0}.aps_bomitem t5 on t.APSBOMItemID=t5.ID "
                            + " left join {0}.wms_lineedgelibrary t1 on t.WMSLineEdgeID=t1.ID "
                            + "WHERE  1=1  "
                            + "and ( :wID <= 0 or :wID = t.ID ) "
                            + "and ( :wWMSLineEdgeID <= 0 or :wWMSLineEdgeID = t.WMSLineEdgeID ) "
                            + "and ( :wProductID <= 0 or :wProductID = t.ProductID ) "
                            + "and ( :wLineID <= 0 or :wLineID = t.LineID ) "
                            + "and ( :wCustomerID <= 0 or :wCustomerID = t.CustomerID ) "
                            + "and ( :wOrderID <= 0 or :wOrderID = t.OrderID ) "
                            + "and ( :wPartID <= 0 or :wPartID = t.PartID ) "
                            + "and ( :wPartPointID <= 0 or :wPartPointID = t.PartPointID ) "
                            + "and ( :wMaterialID <= 0 or :wMaterialID = t.MaterialID ) "
                            + "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=  " +
                            "t.CreateTime ) "
                            + "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=  " +
                            "t.CreateTime );",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wWMSLineEdgeID", wWMSLineEdgeID);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderID);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wMaterialID", wMaterialID);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);
            for (Map<String, Object> wReader : wQueryResult) {
                WMSLineEdgeLibraryItem wItem = new WMSLineEdgeLibraryItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.WMSLineEdgeID = StringUtils.parseInt(wReader.get("WMSLineEdgeID"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.CustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.UseTrackingNumber = StringUtils.parseString(wReader.get("UseTrackingNumber"));
                wItem.IsPost = StringUtils.parseInt(wReader.get("IsPost"));
                wItem.PostMsg = StringUtils.parseString(wReader.get("PostMsg"));
                wItem.APSBOMItemID = StringUtils.parseInt(wReader.get("APSBOMItemID"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceTypeTxt = APSReplaceType.getEnumType(wItem.ReplaceType).getLable();
                wItem.OutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));
                wItem.OutSourceTypeText = APSOutSourceType.getEnumType(wItem.OutSourceType).getLable();
                wItem.ProjectStockNo = StringUtils.parseString(wReader.get("ProjectStockNo"));

                wItem.ProductNo = APSConstans.GetFPCProductNo(wItem.ProductID);
                wItem.LineName = APSConstans.GetFMCLineName(wItem.LineID);
                wItem.CustomerName = APSConstans.GetCRMCustomerName(wItem.CustomerID);
                wItem.PartName = APSConstans.GetFPCPartName(wItem.PartID);
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.CreateTimeText = wItem.CreateTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.CreateTime.getTime())
                        : "";

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 条件查询集合
     */
    public List<WMSLineEdgeLibraryItem> SelectListByAPSBOMItemID(BMSEmployee wLoginUser, int wAPSBOMItemID,
                                                                 OutResult<Integer> wErrorCode) {
        List<WMSLineEdgeLibraryItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (wAPSBOMItemID <= 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT t.*,t1.ProjectStockNo FROM {0}.wms_lineedgelibraryitem t "
                            + " left join {0}.wms_lineedgelibrary t1 on t.WMSLineEdgeID=t1.ID "
                            + "WHERE  1=1  "
                            + "and ( :wAPSBOMItemID <= 0 or :wAPSBOMItemID = t.APSBOMItemID ) ",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wAPSBOMItemID", wAPSBOMItemID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);
            for (Map<String, Object> wReader : wQueryResult) {
                WMSLineEdgeLibraryItem wItem = new WMSLineEdgeLibraryItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.WMSLineEdgeID = StringUtils.parseInt(wReader.get("WMSLineEdgeID"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.CustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.UseTrackingNumber = StringUtils.parseString(wReader.get("UseTrackingNumber"));
                wItem.IsPost = StringUtils.parseInt(wReader.get("IsPost"));
                wItem.PostMsg = StringUtils.parseString(wReader.get("PostMsg"));
                wItem.APSBOMItemID = StringUtils.parseInt(wReader.get("APSBOMItemID"));
                wItem.ProjectStockNo = StringUtils.parseString(wReader.get("ProjectStockNo"));

                wItem.ProductNo = APSConstans.GetFPCProductNo(wItem.ProductID);
                wItem.LineName = APSConstans.GetFMCLineName(wItem.LineID);
                wItem.CustomerName = APSConstans.GetCRMCustomerName(wItem.CustomerID);
                wItem.PartName = APSConstans.GetFPCPartName(wItem.PartID);
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.CreateTimeText = wItem.CreateTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.CreateTime.getTime())
                        : "";

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 查询配送单明细子项
     */
    public int WMS_QueryWMSPickDemandItemID(BMSEmployee wLoginUser, WMSLineEdgeLibraryItem wWMSLineEdgeLibraryItem,
                                            OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT t1.ID FROM {0}.wms_pickdemanditem t1 LEFT JOIN {0}" +
                            ".wms_pickdemand t2 " +
                            "ON t1.DemandID=t2.ID WHERE t1.MaterialID=:MaterialID AND t1.PartID=:PartID AND t1" +
                            ".PartPointID=:PartPointID " +
                            "AND t2.OrderID=:OrderID AND t1.BOMNumber=:FQTY LIMIT 1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("MaterialID", wWMSLineEdgeLibraryItem.MaterialID);
            wParamMap.put("PartID", wWMSLineEdgeLibraryItem.PartID);
            wParamMap.put("PartPointID", wWMSLineEdgeLibraryItem.PartPointID);
            wParamMap.put("OrderID", wWMSLineEdgeLibraryItem.OrderID);
            wParamMap.put("FQTY", wWMSLineEdgeLibraryItem.FQTY);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("ID"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查询台车BOM子项ID
     */
    public int WMS_QueryAPSBOMItemID(BMSEmployee wLoginUser, WMSLineEdgeLibraryItem wWMSLineEdgeLibraryItem,
                                     OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("select ID from {0}.aps_bomitem where orderid=:orderid " +
                            "and partid=:partid and PartPointID=:PartPointID and MaterialID=:MaterialID and " +
                            "DeleteID='''' limit 1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("orderid", wWMSLineEdgeLibraryItem.OrderID);
            wParamMap.put("partid", wWMSLineEdgeLibraryItem.PartID);
            wParamMap.put("PartPointID", wWMSLineEdgeLibraryItem.PartPointID);
            wParamMap.put("MaterialID", wWMSLineEdgeLibraryItem.MaterialID);
            // wParamMap.put("Number", wWMSLineEdgeLibraryItem.FQTY);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseInt(wReader.get("ID"));
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<WMSLineEdgeLibraryItem> SelectErrorList(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        List<WMSLineEdgeLibraryItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);

            String wSQL = MessageFormat.format("SELECT * FROM {0}.wms_lineedgelibraryitem WHERE  1=1  "
                            + "and IsPost not in (1,3);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);
            for (Map<String, Object> wReader : wQueryResult) {
                WMSLineEdgeLibraryItem wItem = new WMSLineEdgeLibraryItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.WMSLineEdgeID = StringUtils.parseInt(wReader.get("WMSLineEdgeID"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.CustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.UseTrackingNumber = StringUtils.parseString(wReader.get("UseTrackingNumber"));
                wItem.IsPost = StringUtils.parseInt(wReader.get("IsPost"));
                wItem.PostMsg = StringUtils.parseString(wReader.get("PostMsg"));
                wItem.APSBOMItemID = StringUtils.parseInt(wReader.get("APSBOMItemID"));

                wItem.ProductNo = APSConstans.GetFPCProductNo(wItem.ProductID);
                wItem.LineName = APSConstans.GetFMCLineName(wItem.LineID);
                wItem.CustomerName = APSConstans.GetCRMCustomerName(wItem.CustomerID);
                wItem.PartName = APSConstans.GetFPCPartName(wItem.PartID);
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.CreateTimeText = wItem.CreateTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.CreateTime.getTime())
                        : "";

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 根据订单ID查询，台车线边库消耗明细
     */
    public List<WMSLineEdgeLibraryItem> SelectListByOrderID(BMSEmployee wLoginUser, int wAPSBOMItemID,
                                                            OutResult<Integer> wErrorCode) {
        List<WMSLineEdgeLibraryItem> wResult = new ArrayList<WMSLineEdgeLibraryItem>();
        try {
            wResult = this.SelectListByAPSBOMItemID(wLoginUser, wAPSBOMItemID, wErrorCode);
        } catch (Exception e) {
            logger.error(e.toString());
        }
        return wResult;
    }

    public List<WMSLineEdgeLibraryItem> SelectListByPage(BMSEmployee wLoginUser, int wProductID, int wLineID,
                                                         int wCustomerID, String wOrderIDs, int wPartID,
                                                         int wPartPointID, String wMaterial, int wAreaID, int wIsPost,
                                                         String wBatchNumber, String wPostMsgLike,
                                                         Calendar wStartTime, Calendar wEndTime,
                                                         Pagination wPagination,
                                                         OutResult<Integer> wErrorCode) {
        List<WMSLineEdgeLibraryItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT t.*,t1.MaterialName,t1.MaterialNo,t1.BatchNumber,t2.Name " +
                            "AreaName,t3.MinPacking,t4.Name UnitText,t5.ReplaceType,t5.OutSourceType,t1.Stock,t1.ProjectStockNo " +
                            "FROM {0}" +
                            ".wms_lineedgelibraryitem t "
                            + " left join {0}.wms_lineedgelibrary t1 on t.WMSLineEdgeID=t1.ID "
                            + " left join {0}.bms_department t2 on t1.AreaID=t2.ID "
                            + " left join {0}.mss_material t3 on t1.MaterialID=t3.ID "
                            + " left join {0}.cfg_unit t4 on t3.CYUnitID=t4.ID "
                            + " left join {0}.aps_bomitem t5 on t.APSBOMItemID=t5.ID "
                            + "WHERE  1=1  "
                            + "and ( :wProductID <= 0 or :wProductID = t.ProductID ) "
                            + "and ( :wLineID <= 0 or :wLineID = t.LineID ) "
                            + "and ( :wCustomerID <= 0 or :wCustomerID = t.CustomerID ) "
                            + "and ( :wOrderID is null or :wOrderID = '''' or t.OrderID in ({1})) "
                            + "and ( :wPartID <= 0 or :wPartID = t.PartID ) "
                            + "and ( :wPartPointID <= 0 or :wPartPointID = t.PartPointID ) "
                            + "and ( :wAreaID <= 0 or :wAreaID = t1.AreaID ) "
                            + "and ( :wIsPost <= 0 or :wIsPost = t.IsPost ) "
                            + "and ( :wMaterial is null or :wMaterial = '''' or ( t1.MaterialNo like ''%{2}%'' or t1" +
                            ".MaterialName like ''%{2}%'' ) ) "
                            + "and ( :wPostMsgLike is null or :wPostMsgLike = '''' or ( t.PostMsg like ''%{3}%'' ) ) "
                            + "and ( :wBatchNumber is null or :wBatchNumber = '''' or t1.BatchNumber = :wBatchNumber) "
                            + "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=  " +
                            "t.CreateTime ) "
                            + "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=  " +
                            "t.CreateTime ) ",
                    wInstance.Result, StringUtils.isEmpty(wOrderIDs) ? "0" : wOrderIDs, wMaterial, wPostMsgLike);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderIDs);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wMaterial", wMaterial);
            wParamMap.put("wAreaID", wAreaID);
            wParamMap.put("wIsPost", wIsPost);
            wParamMap.put("wBatchNumber", wBatchNumber);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);
            wParamMap.put("wPostMsgLike", wPostMsgLike);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap, wPagination);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);
            for (Map<String, Object> wReader : wQueryResult) {
                WMSLineEdgeLibraryItem wItem = new WMSLineEdgeLibraryItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.WMSLineEdgeID = StringUtils.parseInt(wReader.get("WMSLineEdgeID"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.CustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.MinPacking = StringUtils.parseDouble(wReader.get("MinPacking"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.UseTrackingNumber = StringUtils.parseString(wReader.get("UseTrackingNumber"));
                wItem.IsPost = StringUtils.parseInt(wReader.get("IsPost"));
                wItem.PostMsg = StringUtils.parseString(wReader.get("PostMsg"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.APSBOMItemID = StringUtils.parseInt(wReader.get("APSBOMItemID"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.AreaName = StringUtils.parseString(wReader.get("AreaName"));
                wItem.UnitText = StringUtils.parseString(wReader.get("UnitText"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceTypeTxt = APSReplaceType.getEnumType(wItem.ReplaceType).getLable();
                wItem.OutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));
                wItem.OutSourceTypeText = APSOutSourceType.getEnumType(wItem.OutSourceType).getLable();
                wItem.ProjectStockNo = StringUtils.parseString(wReader.get("ProjectStockNo"));

                wItem.ProductNo = APSConstans.GetFPCProductNo(wItem.ProductID);
                wItem.LineName = APSConstans.GetFMCLineName(wItem.LineID);
                wItem.CustomerName = APSConstans.GetCRMCustomerName(wItem.CustomerID);
                wItem.PartName = APSConstans.GetFPCPartName(wItem.PartID);
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.CreateTimeText = wItem.CreateTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.CreateTime.getTime())
                        : "";

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public List<WMSLineEdgeLibraryItem> SelectListWithoutPage(BMSEmployee wLoginUser, int wProductID, int wLineID,
                                                              int wCustomerID, String wOrderIDs, int wPartID,
                                                              int wPartPointID, String wMaterial, int wAreaID,
                                                              int wIsPost, String wBatchNumber, Calendar wStartTime,
                                                              Calendar wEndTime, OutResult<Integer> wErrorCode) {
        List<WMSLineEdgeLibraryItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format("SELECT t.*,t1.MaterialName,t1.MaterialNo,t1.BatchNumber,t2.Name " +
                            "AreaName,t3.MinPacking,t4.Name UnitText,t5.ReplaceType,t5.OutSourceType,t1.Stock,t1.ProjectStockNo " +
                            "FROM {0}" +
                            ".wms_lineedgelibraryitem t "
                            + " left join {0}.wms_lineedgelibrary t1 on t.WMSLineEdgeID=t1.ID "
                            + " left join {0}.bms_department t2 on t1.AreaID=t2.ID "
                            + " left join {0}.mss_material t3 on t1.MaterialID=t3.ID "
                            + " left join {0}.cfg_unit t4 on t3.CYUnitID=t4.ID "
                            + " left join {0}.aps_bomitem t5 on t.APSBOMItemID=t5.ID "
                            + "WHERE  1=1  "
                            + "and ( :wProductID <= 0 or :wProductID = t.ProductID ) "
                            + "and ( :wLineID <= 0 or :wLineID = t.LineID ) "
                            + "and ( :wCustomerID <= 0 or :wCustomerID = t.CustomerID ) "
                            + "and ( :wOrderID is null or :wOrderID = '''' or t.OrderID in ({1})) "
                            + "and ( :wPartID <= 0 or :wPartID = t.PartID ) "
                            + "and ( :wPartPointID <= 0 or :wPartPointID = t.PartPointID ) "
                            + "and ( :wAreaID <= 0 or :wAreaID = t1.AreaID ) "
                            + "and ( :wIsPost <= 0 or :wIsPost = t.IsPost ) "
                            + "and ( :wMaterial is null or :wMaterial = '''' or ( t1.MaterialNo like ''%{2}%'' or t1" +
                            ".MaterialName like ''%{2}%'' ) ) "
                            + "and ( :wBatchNumber is null or :wBatchNumber = '''' or t1.BatchNumber = :wBatchNumber) "
                            + "and ( :wStartTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wStartTime <=  " +
                            "t.CreateTime ) "
                            + "and ( :wEndTime <=str_to_date(''2010-01-01'', ''%Y-%m-%d'')  or :wEndTime >=  " +
                            "t.CreateTime ) ",
                    wInstance.Result, StringUtils.isEmpty(wOrderIDs) ? "0" : wOrderIDs, wMaterial);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wCustomerID", wCustomerID);
            wParamMap.put("wOrderID", wOrderIDs);
            wParamMap.put("wPartID", wPartID);
            wParamMap.put("wPartPointID", wPartPointID);
            wParamMap.put("wMaterial", wMaterial);
            wParamMap.put("wAreaID", wAreaID);
            wParamMap.put("wIsPost", wIsPost);
            wParamMap.put("wBatchNumber", wBatchNumber);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SimpleDateFormat wSDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            Calendar wBaseTime1 = Calendar.getInstance();
            wBaseTime1.set(2010, 0, 1, 0, 0, 0);
            for (Map<String, Object> wReader : wQueryResult) {
                WMSLineEdgeLibraryItem wItem = new WMSLineEdgeLibraryItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.WMSLineEdgeID = StringUtils.parseInt(wReader.get("WMSLineEdgeID"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.CustomerID = StringUtils.parseInt(wReader.get("CustomerID"));
                wItem.OrderID = StringUtils.parseInt(wReader.get("OrderID"));
                wItem.WBSNo = StringUtils.parseString(wReader.get("WBSNo"));
                wItem.PartNo = StringUtils.parseString(wReader.get("PartNo"));
                wItem.PartID = StringUtils.parseInt(wReader.get("PartID"));
                wItem.PartPointID = StringUtils.parseInt(wReader.get("PartPointID"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.MinPacking = StringUtils.parseDouble(wReader.get("MinPacking"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.UseTrackingNumber = StringUtils.parseString(wReader.get("UseTrackingNumber"));
                wItem.IsPost = StringUtils.parseInt(wReader.get("IsPost"));
                wItem.PostMsg = StringUtils.parseString(wReader.get("PostMsg"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.APSBOMItemID = StringUtils.parseInt(wReader.get("APSBOMItemID"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.AreaName = StringUtils.parseString(wReader.get("AreaName"));
                wItem.UnitText = StringUtils.parseString(wReader.get("UnitText"));
                wItem.ReplaceType = StringUtils.parseInt(wReader.get("ReplaceType"));
                wItem.ReplaceTypeTxt = APSReplaceType.getEnumType(wItem.ReplaceType).getLable();
                wItem.OutSourceType = StringUtils.parseInt(wReader.get("OutSourceType"));
                wItem.OutSourceTypeText = APSOutSourceType.getEnumType(wItem.OutSourceType).getLable();
                wItem.ProjectStockNo = StringUtils.parseString(wReader.get("ProjectStockNo"));

                wItem.ProductNo = APSConstans.GetFPCProductNo(wItem.ProductID);
                wItem.LineName = APSConstans.GetFMCLineName(wItem.LineID);
                wItem.CustomerName = APSConstans.GetCRMCustomerName(wItem.CustomerID);
                wItem.PartName = APSConstans.GetFPCPartName(wItem.PartID);
                wItem.PartPointName = APSConstans.GetFPCPartPointName(wItem.PartPointID);
                wItem.CreateTimeText = wItem.CreateTime.compareTo(wBaseTime1) > 0
                        ? wSDF.format(wItem.CreateTime.getTime())
                        : "";

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public MyExcelSheet GetMyExcelSheet(List<WMSLineEdgeLibraryItem> wList) {
        MyExcelSheet wResult = new MyExcelSheet();
        try {
            wResult.SheetName = "物料使用明细台账";
            wResult.TitleName = wResult.SheetName;
            wResult.HeaderList = new ArrayList<>(Arrays.asList("行号", "车型", "修程", "局段", "车号", "工区", "工位", "工序", "物料编号"
                    , "物料名称", "批次号", "单位", "消耗数量", "必换偶换"
                    , "必修偶修", "使用单号", "最小包装", "库存地点", "过账WBS", "过账标识", "过账时刻"));
            wResult.DataList = new ArrayList<>();
            int wIndex = 1;
            List<String> wValueList;
            for (WMSLineEdgeLibraryItem wWMSLineEdgeLibraryItem : wList) {
                wValueList = new ArrayList<>(Arrays.asList(String.valueOf(wIndex), wWMSLineEdgeLibraryItem.ProductNo,
                        wWMSLineEdgeLibraryItem.LineName, wWMSLineEdgeLibraryItem.CustomerName,
                        wWMSLineEdgeLibraryItem.PartNo.split("#")[1], wWMSLineEdgeLibraryItem.AreaName,
                        wWMSLineEdgeLibraryItem.PartName, wWMSLineEdgeLibraryItem.PartPointName,
                        wWMSLineEdgeLibraryItem.MaterialNo, wWMSLineEdgeLibraryItem.MaterialName
                        , wWMSLineEdgeLibraryItem.BatchNumber,
                        wWMSLineEdgeLibraryItem.UnitText, String.valueOf(wWMSLineEdgeLibraryItem.FQTY),
                        wWMSLineEdgeLibraryItem.ReplaceTypeTxt, wWMSLineEdgeLibraryItem.OutSourceTypeText,
                        wWMSLineEdgeLibraryItem.UseTrackingNumber, String.valueOf(wWMSLineEdgeLibraryItem.MinPacking)
                        , wWMSLineEdgeLibraryItem.Stock, wWMSLineEdgeLibraryItem.ProjectStockNo,
                        wWMSLineEdgeLibraryItem.IsPost == 1 ? "成功" : wWMSLineEdgeLibraryItem.IsPost == 0 ? "暂未推送" :
                                wWMSLineEdgeLibraryItem.PostMsg,
                        wWMSLineEdgeLibraryItem.CreateTimeText));
                wResult.DataList.add(wValueList);
                wIndex++;
            }
        } catch (Exception ex) {
            logger.error("GetMyExcelSheet", ex);
        }
        return wResult;
    }

    public double WMS_QueryTotalCount(BMSEmployee wLoginUser, int wWMSLineEdgeID, OutResult<Integer> wErrorCode) {
        double wResult = 0.0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT SUM(FQTY) TotalNumber FROM {0}.`wms_lineedgelibraryitem` WHERE " +
                            "WMSLineEdgeID=:WMSLineEdgeID;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("WMSLineEdgeID", wWMSLineEdgeID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                wResult = StringUtils.parseDouble(wReader.get("TotalNumber"));
                return wResult;
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
